SELECT 1 AS one WHERE 1 IN (SELECT 1);
SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1);
SELECT 1 AS zero WHERE 1 IN (SELECT 2);
SELECT * FROM (SELECT 1 AS x) ss;
SELECT * FROM ((SELECT 1 AS x)) ss;
(SELECT 2) UNION SELECT 2;
((SELECT 2)) UNION SELECT 2;
SELECT ((SELECT 2) UNION SELECT 2);
SELECT (((SELECT 2)) UNION SELECT 2);
SELECT (SELECT ARRAY[1,2,3])[1];
SELECT ((SELECT ARRAY[1,2,3]))[2];
SELECT (((SELECT ARRAY[1,2,3])))[3];
CREATE TABLE SUBSELECT_TBL (  f1 integer,  f2 integer,  f3 float);
INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3);
INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4);
INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5);
INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1);
INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2);
INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3);
INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8);
INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL);
SELECT '' AS eight, * FROM SUBSELECT_TBL;
SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL  WHERE f1 IN (SELECT 1);
SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL  WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL);
SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL  WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE    f2 IN (SELECT f1 FROM SUBSELECT_TBL));
SELECT '' AS three, f1, f2  FROM SUBSELECT_TBL  WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL                         WHERE f3 IS NOT NULL);
SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field"  FROM SUBSELECT_TBL upper  WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1);
SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"  FROM SUBSELECT_TBL upper  WHERE f1 IN    (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3);
SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field"  FROM SUBSELECT_TBL upper  WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL               WHERE f2 = CAST(f3 AS integer));
SELECT '' AS five, f1 AS "Correlated Field"  FROM SUBSELECT_TBL  WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL                     WHERE f3 IS NOT NULL);
SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"  FROM SUBSELECT_TBL ss  WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL                   WHERE f1 != ss.f1 AND f1 < 2147483647);
select q1, float8(count(*)) / (select count(*) from int8_tbl)from int8_tbl group by q1 order by q1;
SELECT *, pg_typeof(f1) FROM  (SELECT 'foo' AS f1 FROM generate_series(1,3)) ss ORDER BY 1;
explain (verbose, costs off) select '42' union all select '43';
explain (verbose, costs off) select '42' union all select 43;
explain (verbose, costs off)select 1 = all (select (select 1));
select 1 = all (select (select 1));
explain (costs off)select * from int4_tbl o where exists  (select 1 from int4_tbl i where i.f1=o.f1 limit null);
explain (costs off)select * from int4_tbl o where not exists  (select 1 from int4_tbl i where i.f1=o.f1 limit 1);
explain (costs off)select * from int4_tbl o where exists  (select 1 from int4_tbl i where i.f1=o.f1 limit 0);
select count(*) from  (select 1 from tenk1 a   where unique1 IN (select hundred from tenk1 b)) ss;
select count(distinct ss.ten) from  (select ten from tenk1 a   where unique1 IN (select hundred from tenk1 b)) ss;
select count(*) from  (select 1 from tenk1 a   where unique1 IN (select distinct hundred from tenk1 b)) ss;
select count(distinct ss.ten) from  (select ten from tenk1 a   where unique1 IN (select distinct hundred from tenk1 b)) ss;
CREATE TEMP TABLE foo (id integer);
CREATE TEMP TABLE bar (id1 integer, id2 integer);
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (1, 1);
INSERT INTO bar VALUES (2, 2);
INSERT INTO bar VALUES (3, 1);
SELECT * FROM foo WHERE id IN    (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s);
SELECT * FROM foo WHERE id IN    (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s);
SELECT * FROM foo WHERE id IN    (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION                      SELECT id1, id2 FROM bar) AS s);
SELECT * FROM foo WHERE id IN    (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s);
SELECT * FROM foo WHERE id IN    (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s);
SELECT * FROM foo WHERE id IN    (SELECT id2 FROM (SELECT id2 FROM bar UNION                      SELECT id2 FROM bar) AS s);
CREATE TABLE orderstest (    approver_ref integer,    po_ref integer,    ordercanceled boolean);
INSERT INTO orderstest VALUES (1, 1, false);
INSERT INTO orderstest VALUES (66, 5, false);
INSERT INTO orderstest VALUES (66, 6, false);
INSERT INTO orderstest VALUES (66, 7, false);
INSERT INTO orderstest VALUES (66, 1, true);
INSERT INTO orderstest VALUES (66, 8, false);
INSERT INTO orderstest VALUES (66, 1, false);
INSERT INTO orderstest VALUES (77, 1, false);
INSERT INTO orderstest VALUES (1, 1, false);
INSERT INTO orderstest VALUES (66, 1, false);
INSERT INTO orderstest VALUES (1, 1, false);
CREATE VIEW orders_view ASSELECT *,(SELECT CASE   WHEN ord.approver_ref=1 THEN ' END) AS "Approved",(SELECT CASE WHEN ord.ordercanceled THEN 'Canceled' ELSE  (SELECT CASE		WHEN ord.po_ref=1		THEN		 (SELECT CASE				WHEN ord.approver_ref=1				THEN '				ELSE 'Approved'			END)		ELSE 'PO'	END)END) AS "Status",(CASE WHEN ord.ordercanceled THEN 'Canceled' ELSE  (CASE		WHEN ord.po_ref=1		THEN		 (CASE				WHEN ord.approver_ref=1				THEN '				ELSE 'Approved'			END)		ELSE 'PO'	END)END) AS "Status_OK"FROM orderstest ord;
SELECT * FROM orders_view;
DROP TABLE orderstest cascade;
create temp table parts (    partnum     text,    cost        float8);
create temp table shipped (    ttype       char(2),    ordnum      int4,    partnum     text,    value       float8);
create temp view shipped_view as    select * from shipped where ttype = 'wt';
create rule shipped_view_insert as on insert to shipped_view do instead    insert into shipped values('wt', new.ordnum, new.partnum, new.value);
insert into parts (partnum, cost) values (1, 1234.56);
insert into shipped_view (ordnum, partnum, value)    values (0, 1, (select cost from parts where partnum = '1'));
select * from shipped_view;
create rule shipped_view_update as on update to shipped_view do instead    update shipped set partnum = new.partnum, value = new.value        where ttype = new.ttype and ordnum = new.ordnum;
update shipped_view set value = 11    from int4_tbl a join int4_tbl b      on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1))    where ordnum = a.f1;
select * from shipped_view;
select f1, ss1 as relabel from    (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1     from int4_tbl a) ss;
select * from (  select max(unique1) from tenk1 as a  where exists (select 1 from tenk1 as b where b.thousand = a.unique2)) ss;
select * from (  select min(unique1) from tenk1 as a  where not exists (select 1 from tenk1 as b where b.unique2 = 10000)) ss;
create temp table numeric_table (num_col numeric);
insert into numeric_table values (1), (1.000000000000000000001), (2), (3);
create temp table float_table (float_col float8);
insert into float_table values (1), (2), (3);
select * from float_table  where float_col in (select num_col from numeric_table);
select * from numeric_table  where num_col in (select float_col from float_table);
create temp table ta (id int primary key, val int);
insert into ta values(1,1);
insert into ta values(2,2);
create temp table tb (id int primary key, aval int);
insert into tb values(1,1);
insert into tb values(2,1);
insert into tb values(3,2);
insert into tb values(4,2);
create temp table tc (id int primary key, aid int);
insert into tc values(1,1);
insert into tc values(2,2);
select  ( select min(tb.id) from tb    where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_idfrom tc;
create temp table t1 (f1 numeric(14,0), f2 varchar(30));
select * from  (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs   from t1 up) ssgroup by f1,f2,fs;
create temp table table_a(id integer);
insert into table_a values (42);
create temp view view_a as select * from table_a;
select view_a from view_a;
select (select view_a) from view_a;
select (select (select view_a)) from view_a;
select (select (a.*)::text) from view_a a;
select q from (select max(f1) from int4_tbl group by f1 order by f1) q;
with q as (select max(f1) from int4_tbl group by f1 order by f1)  select q from q;
begin;
  delete from roadwhere exists (  select 1  from    int4_tbl cross join    ( select f1, array(select q1 from int8_tbl) as arr      from text_tbl ) ss  where road.name = ss.f1 );
  delete from roadwhere exists (  select 1  from    int4_tbl cross join    ( select f1, array(select q1 from int8_tbl) as arr      from text_tbl ) ss  where road.name = ss.f1 );
rollback;
select  (select sq1) as qq1from  (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy   from int8_tbl) sq0  join  int4_tbl i4 on dummy = i4.f1;
create temp table upsert(key int4 primary key, val text);
insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen';
insert into upsert values(1, 'val') on conflict (key) do update set val = 'seen with subselect ' || (select f1 from int4_tbl where f1 != 0 limit 1)::text;
select * from upsert;
with aa as (select 'int4_tbl' u from int4_tbl limit 1)insert into upsert values (1, 'x'), (999, 'y')on conflict (key) do update set val = (select u from aa)returning *;
create temp table outer_7597 (f1 int4, f2 int4);
insert into outer_7597 values (0, 0);
insert into outer_7597 values (1, 0);
insert into outer_7597 values (0, null);
insert into outer_7597 values (1, null);
create temp table inner_7597(c1 int8, c2 int8);
insert into inner_7597 values(0, null);
select * from outer_7597 where (f1, f2) not in (select * from inner_7597);
create temp table outer_text (f1 text, f2 text);
insert into outer_text values ('a', 'a');
insert into outer_text values ('b', 'a');
insert into outer_text values ('a', null);
insert into outer_text values ('b', null);
create temp table inner_text (c1 text, c2 text);
insert into inner_text values ('a', null);
insert into inner_text values ('123', '456');
select * from outer_text where (f1, f2) not in (select * from inner_text);
explain (verbose, costs off)select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
select '1'::text in (select '1'::name union all select '1'::name);
select * from int8_tbl where q1 in (select c1 from inner_text);
begin;
create function bogus_int8_text_eq(int8, text) returns booleanlanguage sql as 'select  1::text =  2';
create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text);
explain (costs off)select * from int8_tbl where q1 in (select c1 from inner_text);
select * from int8_tbl where q1 in (select c1 from inner_text);
create or replace function bogus_int8_text_eq(int8, text) returns booleanlanguage sql as 'select  1::text =  2 and  1::text =  2';
explain (costs off)select * from int8_tbl where q1 in (select c1 from inner_text);
select * from int8_tbl where q1 in (select c1 from inner_text);
create or replace function bogus_int8_text_eq(int8, text) returns booleanlanguage sql as 'select  2 =  1::text';
explain (costs off)select * from int8_tbl where q1 in (select c1 from inner_text);
select * from int8_tbl where q1 in (select c1 from inner_text);
rollback;
  select a.thousand from tenk1 a, tenk1 bwhere a.thousand = b.thousand  and exists ( select 1 from tenk1 c where b.hundred = c.hundred                   and not exists ( select 1 from tenk1 d                                    where a.thousand = d.thousand ) );
  select a.thousand from tenk1 a, tenk1 bwhere a.thousand = b.thousand  and exists ( select 1 from tenk1 c where b.hundred = c.hundred                   and not exists ( select 1 from tenk1 d                                    where a.thousand = d.thousand ) );
explain (verbose, costs off)  select x, x from    (select (select now()) as x from (values(1),(2)) v(y)) ss;
explain (verbose, costs off)  select x, x from    (select (select random()) as x from (values(1),(2)) v(y)) ss;
explain (verbose, costs off)  select x, x from    (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss;
explain (verbose, costs off)  select x, x from    (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss;
explain (verbose, costs off)select sum(ss.tst::int) from  onek o cross join lateral (  select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst,         random() as r  from onek i where i.unique1 = o.unique1 ) sswhere o.ten = 0;
select sum(ss.tst::int) from  onek o cross join lateral (  select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst,         random() as r  from onek i where i.unique1 = o.unique1 ) sswhere o.ten = 0;
explain (costs off)select count(*) from  onek o cross join lateral (    select * from onek i1 where i1.unique1 = o.unique1    except    select * from onek i2 where i2.unique1 = o.unique2  ) sswhere o.ten = 1;
select count(*) from  onek o cross join lateral (    select * from onek i1 where i1.unique1 = o.unique1    except    select * from onek i2 where i2.unique1 = o.unique2  ) sswhere o.ten = 1;
explain (costs off)select sum(o.four), sum(ss.a) from  onek o cross join lateral (    with recursive x(a) as      (select o.four as a       union       select a + 1 from x       where a < 10)    select * from x  ) sswhere o.ten = 1;
select sum(o.four), sum(ss.a) from  onek o cross join lateral (    with recursive x(a) as      (select o.four as a       union       select a + 1 from x       where a < 10)    select * from x  ) sswhere o.ten = 1;
create temp table notinouter (a int);
create temp table notininner (b int not null);
insert into notinouter values (null), (1);
select * from notinouter where a not in (select b from notininner);
create temp table nocolumns();
select exists(select * from nocolumns);
select val.x  from generate_series(1,10) as s(i),  lateral (    values ((select s.i + 1)), (s.i + 101)  ) as val(x)where s.i < 10 and (select val.x) < 110;
explain (verbose, costs off)select * from(values  (3 not in (select * from (values (1), (2)) ss1)),  (false)) ss;
select * from(values  (3 not in (select * from (values (1), (2)) ss1)),  (false)) ss;
explain (verbose, costs off)select * from int4_tbl where  (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in  (select ten from tenk1 b);
select * from int4_tbl where  (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in  (select ten from tenk1 b);
explain (verbose, costs off)select * from int4_tbl o where (f1, f1) in  (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);
select * from int4_tbl o where (f1, f1) in  (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);
select (select q from         (select 1,2,3 where f1 > 0          union all          select 4,5,6.0 where f1 <= 0         ) q )from int4_tbl;
explain (verbose, costs off)select * from    int4_tbl i4,    lateral (        select i4.f1 > 1 as b, 1 as id        from (select random() order by 1) as t1      union all        select true as b, 2 as id    ) as t2where b and f1 >= 0;
select * from    int4_tbl i4,    lateral (        select i4.f1 > 1 as b, 1 as id        from (select random() order by 1) as t1      union all        select true as b, 2 as id    ) as t2where b and f1 >= 0;
create temp sequence ts1;
select * from  (select distinct ten from tenk1) ss  where ten < 10 + nextval('ts1')  order by 1;
select nextval('ts1');
create function tattle(x int, y int) returns boolvolatile language plpgsql as begin  raise notice 'x = %, y = %', x, y;
  return x > y;
end;
explain (verbose, costs off)select * from  (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss  where tattle(x, 8);
select * from  (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss  where tattle(x, 8);
alter function tattle(x int, y int) stable;
explain (verbose, costs off)select * from  (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss  where tattle(x, 8);
select * from  (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss  where tattle(x, 8);
explain (verbose, costs off)select * from  (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss  where tattle(x, u);
select * from  (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss  where tattle(x, u);
drop function tattle(x int, y int);
create table sq_limit (pk int primary key, c1 int, c2 int);
insert into sq_limit values    (1, 1, 1),    (2, 2, 2),    (3, 3, 3),    (4, 4, 4),    (5, 1, 1),    (6, 2, 2),    (7, 3, 3),    (8, 4, 4);
create function explain_sq_limit() returns setof text language plpgsql asdeclare ln text;
begin    for ln in        explain (analyze, summary off, timing off, costs off)        select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3    loop        ln := regexp_replace(ln, 'Memory: \S*',  'Memory: xxx');
        return next ln;
    end loop;
end;
;
select * from explain_sq_limit();
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
drop function explain_sq_limit();
drop table sq_limit;
begin;
declare c1 scroll cursor for select * from generate_series(1,4) i  where i <> all (values (2),(3));
move forward all in c1;
fetch backward all in c1;
commit;
explain (verbose, costs off)with x as (select * from (select f1 from subselect_tbl) ss)select * from x where f1 = 1;
explain (verbose, costs off)with x as materialized (select * from (select f1 from subselect_tbl) ss)select * from x where f1 = 1;
explain (verbose, costs off)with x as (select * from (select f1, now() from subselect_tbl) ss)select * from x where f1 = 1;
explain (verbose, costs off)with x as (select * from (select f1, random() from subselect_tbl) ss)select * from x where f1 = 1;
explain (verbose, costs off)with x as (select * from (select f1 from subselect_tbl for update) ss)select * from x where f1 = 1;
explain (verbose, costs off)with x as (select * from (select f1, now() as n from subselect_tbl) ss)select * from x, x x2 where x.n = x2.n;
explain (verbose, costs off)with x as not materialized (select * from (select f1, now() as n from subselect_tbl) ss)select * from x, x x2 where x.n = x2.n;
explain (verbose, costs off)with recursive x(a) as  ((values ('a'), ('b'))   union all   (with z as not materialized (select * from x)    select z.a || z1.a as a from z cross join z as z1    where length(z.a || z1.a) < 5))select * from x;
with recursive x(a) as  ((values ('a'), ('b'))   union all   (with z as not materialized (select * from x)    select z.a || z1.a as a from z cross join z as z1    where length(z.a || z1.a) < 5))select * from x;
explain (verbose, costs off)with recursive x(a) as  ((values ('a'), ('b'))   union all   (with z as not materialized (select * from x)    select z.a || z.a as a from z    where length(z.a || z.a) < 5))select * from x;
with recursive x(a) as  ((values ('a'), ('b'))   union all   (with z as not materialized (select * from x)    select z.a || z.a as a from z    where length(z.a || z.a) < 5))select * from x;
explain (verbose, costs off)with x as (select * from int4_tbl)select * from (with y as (select * from x) select * from y) ss;
explain (verbose, costs off)with x as materialized (select * from int4_tbl)select * from (with y as (select * from x) select * from y) ss;
explain (verbose, costs off)with x as (select 1 as y)select * from (with x as (select 2 as y) select * from x) ss;
explain (verbose, costs off)with x as (select * from subselect_tbl)select * from x for update;
